// DatabaseHelper.ts
import relationalStore from '@ohos.data.relationalStore';
import common from '@ohos.app.ability.common';

const DB_NAME = 'food_ranking.db';
const TABLE_NAME = 'food_ranking';

export class DatabaseHelper {
  private rdbStore: relationalStore.RdbStore | null = null;

  // 初始化数据库
  async initDatabase(context: common.Context) {
    const config: relationalStore.StoreConfig = {
      name: DB_NAME,
      securityLevel: relationalStore.SecurityLevel.S1
    };

    const sql = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      city TEXT NOT NULL,
      food TEXT NOT NULL,
      popularity INTEGER NOT NULL CHECK(popularity >= 1 AND popularity <= 100)
    )`;

    try {
      this.rdbStore = await relationalStore.getRdbStore(context, config);
      await this.rdbStore.executeSql(sql);
      console.info('Database initialized successfully');
    } catch (err) {
      console.error(`Failed to initialize database: ${err}`);
    }
  }

  // 添加美食记录
  async addFoodRecord(city: string, food: string, popularity: number): Promise<number> {
    if (!this.rdbStore) return -1;

    const valueBucket = {
      'city': city,
      'food': food,
      'popularity': popularity
    };

    try {
      const rowId = await this.rdbStore.insert(TABLE_NAME, valueBucket);
      console.info(`Added food record with rowId: ${rowId}`);
      return rowId;
    } catch (err) {
      console.error(`Failed to add food record: ${err}`);
      return -1;
    }
  }

  // 更新美食记录
  async updateFoodRecord(id: number, city: string, food: string, popularity: number): Promise<boolean> {
    if (!this.rdbStore) return false;

    const valueBucket = {
      'city': city,
      'food': food,
      'popularity': popularity
    };

    const predicates = new relationalStore.RdbPredicates(TABLE_NAME);
    predicates.equalTo('id', id);

    try {
      const rowsAffected = await this.rdbStore.update(valueBucket, predicates);
      console.info(`Updated ${rowsAffected} records`);
      return rowsAffected > 0;
    } catch (err) {
      console.error(`Failed to update food record: ${err}`);
      return false;
    }
  }

  // 删除美食记录
  async deleteFoodRecord(id: number): Promise<boolean> {
    if (!this.rdbStore) return false;

    const predicates = new relationalStore.RdbPredicates(TABLE_NAME);
    predicates.equalTo('id', id);

    try {
      const rowsAffected = await this.rdbStore.delete(predicates);
      console.info(`Deleted ${rowsAffected} records`);
      return rowsAffected > 0;
    } catch (err) {
      console.error(`Failed to delete food record: ${err}`);
      return false;
    }
  }

  // 查询所有记录（可排序）
  async queryAllFoodRecords(orderBy: string = 'popularity DESC'): Promise<Array<FoodRecord>> {
    if (!this.rdbStore) return [];

    const columns = ['id', 'city', 'food', 'popularity'];
    const predicates = new relationalStore.RdbPredicates(TABLE_NAME);
    (predicates as any).orderBy(orderBy); // 使用类型断言解决orderBy问题

    try {
      const resultSet = await this.rdbStore.query(predicates, columns);
      const records: Array<FoodRecord> = [];
      while (resultSet.goToNextRow()) {
        records.push({
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          city: resultSet.getString(resultSet.getColumnIndex('city')),
          food: resultSet.getString(resultSet.getColumnIndex('food')),
          popularity: resultSet.getLong(resultSet.getColumnIndex('popularity'))
        });
      }
      resultSet.close();
      return records;
    } catch (err) {
      console.error(`Failed to query food records: ${err}`);
      return [];
    }
  }

  // 模糊查询城市名称
  async queryFoodRecordsByCity(cityKeyword: string, orderBy: string = 'popularity DESC'): Promise<Array<FoodRecord>> {
    if (!this.rdbStore) return [];

    const columns = ['id', 'city', 'food', 'popularity'];
    const predicates = new relationalStore.RdbPredicates(TABLE_NAME);
    predicates.like('city', `%${cityKeyword}%`);
    (predicates as any).orderBy(orderBy); // 使用类型断言解决orderBy问题

    try {
      const resultSet = await this.rdbStore.query(predicates, columns);
      const records: Array<FoodRecord> = [];
      while (resultSet.goToNextRow()) {
        records.push({
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          city: resultSet.getString(resultSet.getColumnIndex('city')),
          food: resultSet.getString(resultSet.getColumnIndex('food')),
          popularity: resultSet.getLong(resultSet.getColumnIndex('popularity'))
        });
      }
      resultSet.close();
      return records;
    } catch (err) {
      console.error(`Failed to query food records by city: ${err}`);
      return [];
    }
  }
}

export interface FoodRecord {
  id: number;
  city: string;
  food: string;
  popularity: number;
}